
/*
 * $Id$
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 * Copyright (c) $Date$ Timothy Ginder. All Rights Reserved.
 *
 */

package controller;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Author;
import model.Conference;
import model.Manuscript;
import model.ProgramChair;
import model.Recommendation;
import model.Review;
import model.Reviewer;
import model.SubProgramChair;

/**
 * A class designed to handle all queries for the database.
 * @author Timothy Ginder
 * @version 1.11
 *
 */
public class DataBaseInteactor {

    /**Sets the timeout for query.*/
    private static final int TIMEOUT = 30;

    /**The DataBase.*/
    private DataBase db;

    /**
     * An Empty holding constructor.
     */
    public DataBaseInteactor() {
        try {
            db = new DataBase();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
        }
    }

    /**
     * Getter for Database.
     * @return the database.
     */
    public final DataBase getDataBase() {
        return db;
    }

    /**
     * Queries the database.
     * @param password the user's password.
     * @return the user's user name.
     * @throws SQLException if entry not found.
     */
    public final String getUsername(final String password)
            throws SQLException {

        Statement statement = DataBase.getStatement();
        statement.setQueryTimeout(TIMEOUT);  // set timeout to 30 seconds
        statement.executeUpdate("drop table if exists user");

        ResultSet rsu = statement.executeQuery(
                "select id from user where password = 'password'");

        return rsu.getString(0);
    } //End getUsername(password)

    /**
     * Queries the database.
     * @param id the user's id.
     * @return the user's user name.
     * @throws SQLException if entry not found.
     */
    public final String getUsername(final int id)
            throws SQLException {

        Statement statement = DataBase.getStatement();
        statement.setQueryTimeout(TIMEOUT);  // set timeout to 30 seconds
        statement.executeUpdate("drop table if exists user");

        ResultSet rsu = statement.executeQuery(
                "select id from user where id = 'id'");

        return rsu.getString(0);
    } //End getUsername(ID)

    /**
     * Submits a paper to the database.
     * @param author of the paper.
     * @param conf for the paper.
     * @param man that is.
     * @return if the paper is added.
     */
    @SuppressWarnings({ "static-access", "unused" })
    public final boolean submitManuscript(final Author author,
            final Conference conf, final Manuscript man) {
        boolean res = false;
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            String confname = conf.getName();
            String date = conf.getManDate();
            String papname = man.getName();
            String data = man.getContent();
            String auth = man.getAuthor();
            statement.executeUpdate("insert into conference values "
            + "(confname, date, 'subprogramchair', papname,  data, auth,"
                    + " 'undecided')");
            res = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return res;
    } //End submitManuscript

    /**
     * Returns the contents of a manuscript.
     * @param author of the manuscript.
     * @param conf for the manuscript.
     * @param title name of the paper.
     * @return the contents of the paper.
     */
    @SuppressWarnings("static-access")
    public final String getManuscriptContents(final Author author,
            final Conference conf, final String title) {
        String result = new String();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String papername = rsu.getString("papername");
                String confname = rsu.getString("title");
                String paperauth = rsu.getString("paperauthor");
                String contents = rsu.getString("contents");
                if (conf.getName().toString().matches(confname)
                        && title.matches(papername)
                        && author.getUsername().toString().matches(paperauth)) {
                    result = contents.toString();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getManuscriptContents


    /**
     * Gets the reviews for a particular author.
     * @param author to get reviews from.
     * @param conf to get reviews from.
     * @param title of the paper.
     * @return the list of all reviews.
     */
    @SuppressWarnings("static-access")
    public final ArrayList<String> getReviews(final Author author,
            final Conference conf, final String title) {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String conftitle = rsu.getString("title");
                String review = rsu.getString("review");
                if (conf.getName().toString().matches(conftitle)) {
                    result.add(review);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getReviews

    /**
     * removes a paper from the database.
     * @param author who wants their paper removed.
     * @param conf to remove the paper from.
     * @param man paper to remove.
     * @return if it was removed.
     */
    @SuppressWarnings({ "static-access", "unused" })
    public final boolean removeManuscript(final Author author,
            final Conference conf, final Manuscript man) {
        boolean res = false;
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            String confname = conf.getName();
            String date = conf.getManDate();
            String papname = man.getName();
            String data = man.getContent();
            String auth = man.getAuthor();
            statement.executeUpdate("delete from conference values where "
            + "(confname, date, 'subprogramchair', papname,  data, auth,"
                    + " 'undecided')");
            res = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return res;
    } //End removeManuscript

    /**
     * submits a review.
     * @param rev reviewer of the paper.
     * @param conf for the review.
     * @param review of the paper.
     * @param papername of the paper.
     * @param authorname of the author.
     * @return that it was added.
     */
    @SuppressWarnings({ "static-access", "unused" })
    public final boolean submitReview(final Reviewer rev,
            final Conference conf, final Review review,
            final String papername, final String authorname) {
        boolean res = false;
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            String confname = conf.getName();
            String date = conf.getManDate();
            String papname = papername;
            String auth = authorname;
            String revi = review.getContent();
            statement.executeUpdate("insert into conference values "
            + "(confname, date, 'revviewer', papname,  data, auth,"
                    + "revi, 'undecided')");
            res = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return res;
    } //End submitReview

    /**
     * Gets papers for a reviewer.
     * @param rev the reviewer.
     * @param conf conference it is for.
     * @return list of papers.
     */
    @SuppressWarnings("static-access")
    public final List<String> getAssignedPapers(final Reviewer rev,
            final Conference conf) {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String reviewer = rsu.getString("reviewer");
                String papname = rsu.getString("papername");
                if (rev.getUsername().matches(reviewer)) {
                    result.add(papname);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getAssignedPapers

    /**
     * submits a recommendation.
     * @param subPro of the conference.
     * @param conf for the recommendation.
     * @param man manuscript for evaluation.
     * @param rec to add.
     * @return that it has been added.
     */
    @SuppressWarnings({ "static-access", "unused" })
    public final boolean submitRecommendation(final SubProgramChair subPro,
            final Conference conf, final Manuscript man,
            final Recommendation rec) {
        boolean res = false;
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            String confname = conf.getName();
            String date = conf.getManDate();
            String papname = man.getName();
            String data = man.getContent();
            String auth = man.getAuthor();
            String recomend = rec.getContent();
            statement.executeUpdate("insert into conference values "
            + "(confname, date, 'revviewer', papname,  data, auth,"
                    + "revi, recomend)");
            res = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return res;
        } //End submitRecomendation

    /**
     * Gets the status of each manuscript in the conference.
     * @param conf to get status for.
     * @return the list of strings.
     */
    @SuppressWarnings("static-access")
    public final List<String> getStatus(final Conference conf) {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String confer = rsu.getString("title");
                String stat = rsu.getString("status");
                if (confer.matches("subprogramchair")) {
                    result.add(stat);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getStatus

    /**
     * Gets the status of a single manuscript in a conference.
     * @param conf to get the paper status from.
     * @param man the paper.
     * @return the status as a string.
     */
    @SuppressWarnings("static-access")
    public final String getStatus(final Conference conf,
            final Manuscript man) {
        String result = new String();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String papername = rsu.getString("papername");
                String confname = rsu.getString("title");
                String paperauth = rsu.getString("paperauthor");
                String stat = rsu.getString("status");
                if (conf.getName().toString().matches(confname)
                        && man.getName().toString().matches(papername)
                        && man.getAuthor().toString().matches(paperauth)) {
                    result = stat.toString();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getStatus

    /**
     * Sets the status for the manuscript.
     * @param conf of the paper.
     * @param man the paper.
     * @param status to add.
     * @return that it was added.
     */
    @SuppressWarnings({ "static-access", "unused" })
    public final boolean setStatus(final Conference conf,
            final Manuscript man, final String status) {
        boolean ret = false;
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            String confname = conf.getName();
            String date = conf.getManDate();
            String papname = man.getName();
            String data = man.getContent();
            String auth = man.getAuthor();
            statement.executeUpdate("insert into conference values "
            + "(confname, date, 'subprogramchair', papname,  data, auth,"
                    + " status)");
            ret = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return ret;
    }

    /**
     * Gets all the titles of a conferences papers.
     * @param conf the conference.
     * @return the list.
     */
    @SuppressWarnings("static-access")
    public final List<String> getPaperTitles(final Conference conf) {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String confer = rsu.getString("title");
                String papertit = rsu.getString("papername");
                if (confer.matches("subprogramchair")) {
                    result.add(papertit);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getPaperTitles

    /**
     * Designates a subprogram chair to a paper.
     * @param conf the conference.
     * @param sub the subprogram chair.
     * @param man the paper.
     * @return if it was done.
     */
    @SuppressWarnings({ "static-access", "unused" })
    public final boolean designateSubMan(final Conference conf,
            final SubProgramChair sub, final Manuscript man) {
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            String confname = conf.getName();
            String date = conf.getManDate();
            String papname = man.getName();
            String data = man.getContent();
            String auth = man.getAuthor();
            statement.executeUpdate("insert into conference values "
            + "(confname, date, 'subprogramchair', papname,  data, auth,"
                    + " 'undecided')");
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return true;
    }

    /**
     * Gets a list of subprogram chairs of a conference.
     * @param conf the conference.
     * @return the list.
     */
    @SuppressWarnings("static-access")
    public final List<String> getSubProg(final Conference conf) {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String rol = rsu.getString("role");
                if (rol.matches("subprogramchair")) {
                    result.add(rol);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getSubProg

    /**
     * Adds a new user.
     * @param localusername of the user.
     * @param lastname of the user.
     * @param firstname of the user.
     * @param confname the user is attached to.
     * @param usrrole of the user.
     * @param password of the user.
     * @return true.
     */
    public final boolean newUser(final String localusername,
            final String lastname, final String firstname,
            final String confname, final String usrrole,
            final String password) {
        boolean result = false;
        try {
            String insertTableSQL = "INSERT INTO user"
        + "(username, lastname, firstname, conference, role, password) VALUES"
        + "(?,?,?,?,?,?)";
            PreparedStatement update =
                    db.getConnection().prepareStatement(insertTableSQL);
            update.setString(1, localusername);
            update.setString(2, lastname);
            update.setString((2 + 1), firstname);
            update.setString((2 + 2), confname);
            update.setString((2 + 2 + 1), usrrole);
            update.setString((2 + 2 + 2), password);
            update.executeUpdate();
            result = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    }

    /**
     * Creates a new conference.
     * @param title of the conference.
     * @param date of the conference.
     * @param paperDueDate for the conference.
     * @param reviewDueDate for the conference.
     * @param recDueDate for the conference.
     * @param chair of the conference.
     * @return true.
     */
    @SuppressWarnings("static-access")
    public final boolean makeConference(final String title, final String date,
            final String paperDueDate, final String reviewDueDate,
            final String recDueDate, final ProgramChair chair) {
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            //statement.executeUpdate("insert into conference values "
            //+ "(title, date, chair, ' ', ' ', ' ', ' ', ' ', ' ')");
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return true;
    } // End makeConference()

    /**
     * Gets a full list of all conference names.
     * @return the list of conferences.
     */
    @SuppressWarnings("static-access")
    public final List<String> getConferenceTitles() {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String confer = rsu.getString("title");
                if (!result.contains(confer)) {
                    result.add(confer);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getConferenceTitles()

    /**
     * Gets a full list of all conference dates.
     * @return the list of conference dates.
     */
    @SuppressWarnings("static-access")
    public final List<String> getConferenceDates() {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String confdate = rsu.getString("confdate");
                if (!result.contains(confdate)) {
                    result.add(confdate);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getConferenceDates()

    /**
     * Gets a full list of all conference paper due dates.
     * @return the list of paper due dates.
     */
    @SuppressWarnings("static-access")
    public final List<String> getConferenceManDates() {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String date = rsu.getString("manduedate");
                if (!result.contains(date)) {
                    result.add(date);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getConferenceManDates()

    /**
     * Gets a full list of all conference recommendation due dates.
     * @return the list of recommendation due dates.
     */
    @SuppressWarnings("static-access")
    public final List<String> getConferenceRecDates() {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String date = rsu.getString("recduedate");
                if (!result.contains(date)) {
                    result.add(date);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getConferenceDates()

    /**
     * Gets a full list of all conference review due dates.
     * @return the list of review due dates.
     */
    @SuppressWarnings("static-access")
    public final List<String> getConferenceRevDates() {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String date = rsu.getString("revduedate");
                if (!result.contains(date)) {
                    result.add(date);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getConferenceRevDates()

    /**
     * Gets a full list of all conference Program Chairs.
     * @return the usernames of program chairs.
     */
    @SuppressWarnings("static-access")
    public final List<String> getConferenceProgramChairUN() {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String username = rsu.getString("PCUserName");
                if (!result.contains(username)) {
                    result.add(username);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getConferenceProgramChairUN()

    /**
     * Gets the PW for a program chair.
     * @return the pw.
     */
    @SuppressWarnings("static-access")
    public final List<String> getConferenceProgramChairPW() {
        ArrayList<String> result = new ArrayList<>();
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from conference");
            while (rsu.next()) {
                String pw = rsu.getString("PCPW");
                if (!result.contains(pw)) {
                    result.add(pw);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } //End getConferenceProgramChairPW()

    /**
     * Returns if the login is valid.
     * @param localusername users user name.
     * @param password user's password.
     * @param conf user wants to use.
     * @param role in the conference.
     * @return if the login is valid.
     */
    @SuppressWarnings("static-access")
    public final boolean validLogin(final String localusername,
            final String password, final String conf, final String role) {
        boolean result = false;
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            ResultSet rsu = statement.executeQuery(
                    "select * from user");
            while (rsu.next()) {
                String usname = rsu.getString("username");
                String pswd = rsu.getString("password");
                String confe = rsu.getString("conference");
                String rol = rsu.getString("role");
                if (usname.matches(localusername) && pswd.matches(password)
                        && confe.matches(conf) && rol.matches(role)) {
                    result = true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return result;
    } // End of validLogin();

    /**
     * Gets a person's First name.
     * @param theUsername of the person.
     * @return the first name.
     * @throws SQLException if does not work.
     */
    public final String getFirstName(final String theUsername)
            throws SQLException {
        Statement statement = DataBase.getStatement();
        statement.setQueryTimeout(TIMEOUT);  // set timeout to 30 seconds
        statement.executeUpdate("drop table if exists user");

        ResultSet rsu = statement.executeQuery(
                "select firstname from user where username = 'theUsername'");

        return rsu.getString(0);
    } //End of getFirstName

    /**
     * Gets the person's last name.
     * @param theUsername of the person.
     * @return their last name.
     * @throws SQLException if does not work.
     */
    public final String getLastName(final String theUsername)
            throws SQLException {
        Statement statement = DataBase.getStatement();
        statement.setQueryTimeout(TIMEOUT);  // set timeout to 30 seconds
        statement.executeUpdate("drop table if exists user");

        ResultSet rsu = statement.executeQuery(
                "select lastname from user where username = 'theUsername'");

        return rsu.getString(0);
    } //End of getLastName

    /**
     * Assigns a reviewer to a paper.
     * @param theConference attached to.
     * @param theReviewerUN who will.
     * @param theAuthorUN who will be.
     * @param theManTitle of the paper.
     * @return if done.
     */
    @SuppressWarnings({ "static-access", "unused" })
    public final boolean assignManuscript(
            final Conference theConference, final String theReviewerUN,
            final String theAuthorUN, final String theManTitle) {
        boolean ret = false;
        try {
            Statement statement = db.getStatement();
            statement.setQueryTimeout(TIMEOUT);
            String confname = theConference.getName();
            String date = theConference.getManDate();
            statement.executeUpdate("insert into conference values "
            + "(confname, theReviewerUN, 'Reviewer', theManTitle,"
            + " date, 'subprogramchair', theAuthorUN");
            ret = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }  // set timeout to 30 seconds
        return ret;
    }

} //End of DataBaseInteractor Class
